package top.content;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.StringUtil;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import top.content.utils.FileUtil;
import top.content.utils.Log;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class Main {
    private String[] allowedPicSuffixs = new String[]{".jpg",".png"};
    private String[] allowedExcelSuffixs = new String[]{".xls",".xlsx"};

    private List<File> needDeletedFilesWhenExit = new ArrayList<>();

    private List<File> allPics = null;
    private File excelFile = null;

    // 获取当前文件夹
    private String currentPath = System.getProperty("user.dir"); // + "\\test";

    public Main() throws IOException {
    }

    public static void main(String[] args) throws IOException {

        // 初始化
        Main main = new Main();

        // 查找所有图片，和Excel文件
        main.findAllPicAndExcel();

        // 合并图片到Excel中
        try {
            main.doMerge();
        } catch (IOException e) {
            e.printStackTrace();
        }

        // 清理文件
        main.cleanDir();
    }

    // 查找所有图片，和Excel文件
    private void findAllPicAndExcel() {
        Log.debug("finding photos in " + currentPath);
        File path = new File(currentPath);
        File[] allFiles = path.listFiles();
        if (null != allFiles && allFiles.length > 0) {
            allPics = new ArrayList<>();
            for (File f : allFiles) {
                if (isExcel(f)) {
                    if (null != excelFile) {
                        Log.error("错误，发现多余1个EXCEL文件，请确保只存在一个EXCEL文件！");
                        return;
                    }
                    excelFile = f;
                }

                if (isPic(f)){
                    allPics.add(f);
                }
            }
        }
    }

    private void doMerge() throws IOException {

        // 拷贝一个excel文件出来，在新的excel文件上进行操作
        File newExcel = new File(excelFile.getParent() + "/" + new Date().getTime() + getSuffix(excelFile));
        FileUtil.copyFile(excelFile, newExcel);
        needDeletedFilesWhenExit.add(newExcel);

        // 给新文件设置行高------------------------
        Workbook workbook0 = null;
        if (getSuffix(newExcel).equalsIgnoreCase(allowedExcelSuffixs[0])) {  //Excel 2003
            workbook0 = new HSSFWorkbook(new FileInputStream(newExcel));
        } else if (getSuffix(newExcel).equalsIgnoreCase(allowedExcelSuffixs[1])) {  // Excel 2007/2010
            workbook0 = new XSSFWorkbook(new FileInputStream(newExcel));
        }

        if (null != workbook0) {
            Sheet sheet = workbook0.getSheetAt(0);
            Object value = null;
            Row row = null;

            for (int i = sheet.getFirstRowNum() + 2; i <= sheet.getPhysicalNumberOfRows(); i++) { // 从第三行开始读取
                row = sheet.getRow(i);
                if (null != row){
                    row.setHeightInPoints(83);
                }
            }
            FileOutputStream fileOut = new FileOutputStream(excelFile.getParent() + "/" + "newexcel" + getSuffix(excelFile));
            workbook0.write(fileOut);
            fileOut.close();
        }
        // 给新文件设置行高------------------------

        newExcel = new File(excelFile.getParent() + "/" + "newexcel" + getSuffix(excelFile));
        needDeletedFilesWhenExit.add(newExcel);
        Workbook workbook = null;
        if (getSuffix(newExcel).equalsIgnoreCase(allowedExcelSuffixs[0])) {  //Excel 2003
            workbook = new HSSFWorkbook(new FileInputStream(newExcel));
        } else if (getSuffix(newExcel).equalsIgnoreCase(allowedExcelSuffixs[1])) {  // Excel 2007/2010
            workbook = new XSSFWorkbook(new FileInputStream(newExcel));
        }

        if (null != workbook) {
            Sheet sheet = workbook.getSheetAt(0);
            Object value = null;
            Row row = null;
            Cell cell = null;

            // 画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
            // http://www.bubuko.com/infodetail-2310009.html
            // https://www.cnblogs.com/firstcsharp/p/4896121.html
            XSSFDrawing patriarch = (XSSFDrawing) sheet.createDrawingPatriarch();
            FileOutputStream fileOut = null;

            for (int i = sheet.getFirstRowNum() + 2; i <= sheet.getPhysicalNumberOfRows(); i++) { // 从第三行开始读取
                row = sheet.getRow(i);
                cell = null != row ? row.getCell(0) : null;
                String values = null != cell ? cell.getStringCellValue() : null;
                if (null == values || values.length() == 0){
                    continue;
                }

                File picFile = findPicFileByName(values);
                if (null != picFile) {
                    Log.debug("匹配到图片： ----> " + picFile.getName());
                    BufferedImage bufferImg = null;
                    //先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
                    try {
                        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                        bufferImg = ImageIO.read(picFile);
                        ImageIO.write(bufferImg, "jpg", byteArrayOut);

                        //anchor主要用于设置图片的属性
                        XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 9, 83, 1, i,  2, i + 1);
                        anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
                        //插入图片
                        patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));


                    }catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }

            fileOut = new FileOutputStream(excelFile.getParent() + "\\" + (new Date().getTime()) + getSuffix(excelFile));
            // 写入excel文件
            workbook.write(fileOut);

            if(fileOut != null){
                try {
                    fileOut.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    // 更具关键子查找图片文件
    private File findPicFileByName(String keywords) {
        for (File picFile : allPics) {
            if (picFile.getName().startsWith(keywords)){
                return picFile;
            }
        }
        return null;
    }

    // 判断一个文件是否是图片文件
    private boolean isPic(File file) {
        if (file.exists() && !file.isDirectory()) {
            String suffix = getSuffix(file);
            if (isInArray(allowedPicSuffixs, suffix)){
                return true;
            }
        }
        return false;
    }

    // 判断一个文件是否是Excel文件
    private boolean isExcel(File file) {
        if (file.exists() && !file.isDirectory()) {
            String suffix = getSuffix(file);
            if (isInArray(allowedExcelSuffixs, suffix)){
                return true;
            }
        }
        return false;
    }

    // 判断一个字符串是否在包含在一个字符串数组中
    private boolean isInArray(String[] arry, String o) {
        for (String ob : arry) {
            if (ob.equalsIgnoreCase(o)){
                return true;
            }
        }
        return false;
    }

    // 获取一个文件的后缀名
    private String getSuffix(File file) {
        if (file.exists() && !file.isDirectory()) {
            String suffix = file.getName().substring(file.getName().lastIndexOf("."));
            return suffix;
        }
        return null;
    }

    private void cleanDir() {
        for (File f : needDeletedFilesWhenExit) {
            f.deleteOnExit();
        }
    }
}
